package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbc.common.dao.DAO;
import com.mbc.common.param.CriteriaParams;
import com.mbc.rmt.data.StaffTypeDataList;
import com.mbc.rmt.entities.StaffType;
import com.mbc.rmt.entities.StaffTypeRpt;

public class StaffTypeDAOImpls extends DAO<StaffType> implements StaffTypeDAO {
	public StaffTypeDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "StaffType";
	}

	@Override
	public void create(StaffType entity) throws SQLException {
		query = "insert into "
				+ table_name
				+ " ( StaffTypeId, StaffType_Code, Description, branchId) values( ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getStafftypeId());
		pstmt.setString(i++, entity.getStafftypeCode());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void update(StaffType entity) throws SQLException {
		query = "update " + table_name
				+ " set StaffType_Code = ?, Description = ?, branchId = ?"
				+ " where StaffTypeId = " + entity.getStafftypeId();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setString(i++, entity.getStafftypeCode());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where StaffTypeId = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public StaffType find(long key) throws SQLException {
		StaffType l_StaffType = null;
		query = getSelectQuery() + " where StaffTypeId = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_StaffType = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_StaffType;
	}

	@Override
	public ArrayList<StaffType> findAll() throws SQLException {
		ArrayList<StaffType> l_StaffTypeList = new ArrayList<StaffType>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_StaffTypeList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_StaffTypeList;
	}

	@Override
	protected StaffType readRecord(ResultSet rs) throws SQLException {
		StaffType l_entity = new StaffType();
		l_entity.setStafftypeId(rs.getLong("StaffTypeId"));
		l_entity.setStafftypeCode(rs.getString("StaffType_Code"));
		l_entity.setDescription(rs.getString("Description"));
		l_entity.setBranchId(rs.getLong("branchId"));
		return l_entity;
	}

	@Override
	public StaffTypeDataList findStaffTypes(CriteriaParams cri)
			throws SQLException {
		StaffTypeDataList l_DataList = new StaffTypeDataList();
		ArrayList<StaffType> l_entityList = new ArrayList<StaffType>();
		String l_cri = " and (StaffType_Code like N'%" + cri.getKeyword() + "%'"
				+ " or Description like N'%" + cri.getKeyword() + "%')"
				+ " and BranchId = " + cri.getBranchId();
		query = "set rowcount "
				+ cri.getSize()
				+ " select * from (select row_number() over(order by StaffTypeId) as [rowno], * from "
				+ table_name + " where 1 = 1" + l_cri + ") " + table_name
				+ " where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_entityList.add(readRecord(rs));
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(StaffTypeId) from " + table_name + " where 1 = 1"
				+ l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	public StaffType findByCode(String code,long branchId) throws SQLException {
		StaffType l_StaffType = null;
		query = getSelectQuery() + " where StaffType_Code = '" + code + "' and BranchId = " + branchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_StaffType = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_StaffType;
	}

	public ResultSet prepareStaffTypeReport(StaffTypeRpt info)
			throws SQLException {
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_StaffTypeRpt ?, ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		StaffTypeRptParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;

	}

	private void StaffTypeRptParameter(CallableStatement l_pstmt,
			StaffTypeRpt info) throws SQLException {
		int i = 1;

		l_pstmt.setString(i++, info.getFromStaffTypeID());
		l_pstmt.setString(i++, info.getToStaffTypeID());
		l_pstmt.setLong(i++, info.getBranchId());
	}

	@Override
	public StaffTypeDataList getStaffTypeList(StaffTypeRpt info)
			throws SQLException {
		StaffTypeDataList l_DataList = new StaffTypeDataList();
		ArrayList<StaffType> l_StaffTypeList = new ArrayList<StaffType>();
		String l_query = "dbo.sp_cr_StaffTypeRpt ?, ?, ?";
		CallableStatement l_pstmt = conn.prepareCall(l_query);
		StaffTypeRptParameter(l_pstmt, info);
		ResultSet l_rs = l_pstmt.executeQuery();
		while (l_rs.next()) {
			l_StaffTypeList.add(readRecordFT(l_rs));
		}
		l_DataList.setEntityList(l_StaffTypeList);
		l_rs.close();
		l_pstmt.close();
		return l_DataList;
	}

	private StaffType readRecordFT(ResultSet rs) throws SQLException {
		StaffType l_entity = new StaffType();

		l_entity.setStafftypeCode(rs.getString("StaffType_Code"));
		l_entity.setDescription(rs.getString("Description"));
		//l_entity.setBranchId(rs.getLong("branchId"));
		return l_entity;
	}

}
